package ${packageName}.core;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;
import java.util.concurrent.Future;
import java.util.concurrent.TimeUnit;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import ${packageName}.common.PaginationSupport;

/**
 * 
 * 数据库操作工具类(Apache Dbutil 封装实现)
 * 
 * @author 00fly
 * @version [版本号, ${date?date}]
 * @see [相关类/方法]
 * @since [产品/模块版本]
 */
@SuppressWarnings({"rawtypes", "unchecked"})
public class MySqlDBUtil
{
    private static DataSource dataSource = null;
    
    private static final Logger LOGGER = LoggerFactory.getLogger(MySqlDBUtil.class);
    
    // 使用ThreadLocal存储当前线程中的Connection对象
    private ThreadLocal<Connection> threadLocal = new ThreadLocal<>();
    
    private QueryRunner runner = new QueryRunner();
    
    // 静态初始化 DataSource
    static
    {
        ResourceBundle config = ResourceBundle.getBundle("jdbc");
        PoolProperties p = new PoolProperties();
        p.setUrl(config.getString("jdbc.url"));
        p.setDriverClassName(config.getString("jdbc.driver"));
        p.setUsername(config.getString("jdbc.username"));
        p.setPassword(config.getString("jdbc.password"));
        p.setFairQueue(true);// 异步获取连接,必须将fairQueue设置为true
        p.setJmxEnabled(true);
        p.setTestWhileIdle(false);
        p.setTestOnBorrow(true);
        p.setValidationQuery("SELECT 1");
        p.setTestOnReturn(false);
        p.setValidationInterval(30000);
        p.setTimeBetweenEvictionRunsMillis(30000);
        p.setMaxActive(100);
        p.setInitialSize(10);
        p.setMaxWait(10000);
        p.setRemoveAbandonedTimeout(60);
        p.setMinEvictableIdleTimeMillis(30000);
        p.setMinIdle(10);
        p.setLogAbandoned(true);
        p.setRemoveAbandoned(true);
        p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
        dataSource = new DataSource();
        dataSource.setPoolProperties(p);
    }
    
    /**
     * <默认构造函数>
     */
    private MySqlDBUtil()
    {
        super();
    }
    
    private static final MySqlDBUtil SINGLE = new MySqlDBUtil();
    
    /**
     * 单例获取对象
     * 
     * @return
     * @see [类、类#方法、类#成员]
     */
    public static MySqlDBUtil getInstance()
    {
        return SINGLE;
    }
    
    /**
     * 批量更新
     * 
     * @param sql 需执行的sql
     * @param params List参数组
     * @return
     * @throws SQLException
     * @see [类、类#方法、类#成员]
     */
    public int[] batch(String sql, List<Object[]> params)
        throws SQLException
    {
        Object[][] paramArr = params.toArray(new Object[0][]);
        return batch(sql, paramArr);
    }
    
    /**
     * 批量更新
     * 
     * @param sql 需执行的sql
     * @param params 参数组
     * @return
     * @throws SQLException
     * @see [类、类#方法、类#成员]
     */
    public int[] batch(String sql, Object[][] params)
        throws SQLException
    {
        LOGGER.info("executeBatch: {}, params:{}", sql, ToStringBuilder.reflectionToString(params));
        Connection conn = null;
        try
        {
            conn = getConnection();
            return runner.batch(conn, sql, params);
        }
        catch (SQLException e)
        {
            LOGGER.error("------executeBatch Error------", e);
            throw e;
        }
        finally
        {
            if (conn != null && conn.getAutoCommit())
            {
                close();
            }
            LOGGER.info("DBPool executeBatch end ");
        }
    }
    
    /**
     * 释放数据库连接
     * 
     * @see [类、类#方法、类#成员]
     */
    protected void close()
    {
        LOGGER.info("------释放数据库连接------");
        Connection conn = threadLocal.get();
        DbUtils.closeQuietly(conn);
        threadLocal.remove(); // 解除当前线程上绑定conn
    }
    
    /**
     * 释放数据库连接
     * 
     * @param conn
     */
    protected void close(Connection conn)
    {
        LOGGER.info("------释放数据库连接-------");
        DbUtils.closeQuietly(conn);
        threadLocal.remove(); // 解除当前线程上绑定conn
    }
    
    /**
     * 获取数据库连接
     * 
     * @return
     * @see [类、类#方法、类#成员]
     */
    protected synchronized Connection getConnection()
    {
        Connection connection = threadLocal.get();
        try
        {
            if (connection == null)
            {
                Future<Connection> future = dataSource.getConnectionAsync();
                while (!future.isDone())
                {
                    try
                    {
                        TimeUnit.MICROSECONDS.sleep(100);
                    }
                    catch (InterruptedException x)
                    {
                        Thread.currentThread().interrupt();
                        LOGGER.error(x.getMessage());
                    }
                }
                connection = future.get();
                threadLocal.set(connection); // 把 connection绑定到当前线程上
            }
        }
        catch (Exception e)
        {
            LOGGER.error(e.getMessage(), e);
            throw new RuntimeException("Failed to get DBPool connection.....");
        }
        return connection;
    }
    
    /**
     * 获取事务管理器
     * 
     * @return 事务管理实例
     */
    protected synchronized TransactionManager getTranManager()
    {
        return new TransactionManager(getConnection());
    }
    
    /**
     * 带可变参数, 执行sql插入，返回新增记录的自增主键<BR>
     * 注意： 若插入的表无自增主键则返回 0，异常的话则返回 null
     * 
     * @param sql
     * @param para
     * @return
     * @throws SQLException
     * @see [类、类#方法、类#成员]
     */
    public Long insert(String sql, Object... para)
        throws SQLException
    {
        LOGGER.info("InsertSql: {}, para: {}", sql, ToStringBuilder.reflectionToString(para));
        Connection conn = null;
        Long id = null;
        try
        {
            conn = getConnection();
            id = (Long)runner.insert(conn, sql, new ScalarHandler<Object>(), para);
        }
        catch (SQLException e)
        {
            LOGGER.error("------insertSql error------", e);
            throw e;
        }
        finally
        {
            if (conn != null && conn.getAutoCommit())
            {
                close();
            }
            LOGGER.info("DBPool execute InsertSql end ");
        }
        return id;
    }
    
    /**
     * 带可变参数查询,返回执行结果
     * 
     * @param sql 查询sql
     * @param para 可变参数
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> query(String sql, Object... para)
        throws SQLException
    {
        LOGGER.info("querySql: {}, para: {}", sql, ToStringBuilder.reflectionToString(para));
        Connection conn = null;
        List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
        try
        {
            conn = getConnection();
            result = runner.query(conn, sql, new MapListHandler(), para);
        }
        catch (SQLException e)
        {
            LOGGER.error("------querySql error------", e);
            throw e;
        }
        finally
        {
            if (conn != null && conn.getAutoCommit())
            {
                close();
            }
        }
        return result;
    }
    
    /**
     * 带可变参数查询,返回执行结果
     * 
     * @param clazz
     * @param sql 查询sql
     * @param para 可变参数
     * @return
     * @throws SQLException
     */
    public <T> List query(T clazz, String sql, Object... para)
        throws SQLException
    {
        LOGGER.info("querySql: {}, para: {}", sql, ToStringBuilder.reflectionToString(para));
        Connection conn = null;
        List<T> result = new ArrayList<>();
        try
        {
            conn = getConnection();
            // 下划线分隔的表字段名转换为实体bean驼峰命名属性
            BeanProcessor bean = new GenerousBeanProcessor();
            RowProcessor processor = new BasicRowProcessor(bean);
            result = (List<T>)runner.query(conn, sql, new BeanListHandler((Class)clazz, processor), para);
        }
        catch (SQLException e)
        {
            LOGGER.error("------querySql error------", e);
            throw e;
        }
        finally
        {
            if (conn != null && conn.getAutoCommit())
            {
                close();
            }
        }
        return result;
    }
    
    /**
     * 带可变参数查询,返回首条执行结果
     * 
     * @param sql 查询sql
     * @param para 可变参数
     * @return
     * @throws SQLException
     */
    public Map<String, Object> queryFirst(String sql, Object[] para)
        throws SQLException
    {
        if (!sql.contains(" limit ")) // 前后有空格
        {
            sql = sql + " limit 1";
        }
        List<Map<String, Object>> list = query(sql, para);
        if (list.isEmpty())
        {
            return Collections.emptyMap();
        }
        return list.get(0);
    }
    
    /**
     * 带可变参数查询,返回首条执行结果
     * 
     * @param clazz
     * @param sql 查询sql
     * @param para 可变参数
     * @return
     * @throws SQLException
     */
    public <T> T queryFirst(T clazz, String sql, Object... para)
        throws SQLException
    {
        if (!sql.toLowerCase().contains(" limit ")) // 前后有空格
        {
            sql = sql + " limit 1";
        }
        List<T> list = query((Class)clazz, sql, para);
        if (list.isEmpty())
        {
            return null;
        }
        return list.get(0);
    }
    
    /**
     * 带可变参数查询，返回long类型数据
     * 
     * @param countSql 查询记录条数的sql
     * @param para 可变参数
     * @return
     * @throws SQLException
     */
    public Long queryForLong(String countSql, Object... para)
        throws SQLException
    {
        LOGGER.info("queryForLong: {}, para: {}", countSql, ToStringBuilder.reflectionToString(para));
        Long number = null;
        Connection conn = null;
        try
        {
            conn = getConnection();
            number = runner.query(conn, countSql, new ScalarHandler<Long>(), para);
        }
        catch (SQLException e)
        {
            LOGGER.error("------queryForLong error: {}------", e.getMessage());
            throw e;
        }
        finally
        {
            if (conn != null && conn.getAutoCommit())
            {
                close();
            }
            LOGGER.info("DBPool queryForLong end ");
        }
        return number;
    }
    
    /**
     * 带可变参数条件的分页查询
     * 
     * @param sql 查询sql
     * @param pageNo 页号
     * @param pageSize 每页记录数
     * @param para 可变参数
     * @return
     * @throws SQLException
     * @see [类、类#方法、类#成员]
     */
    public PaginationSupport queryForPagination(String sql, int pageNo, int pageSize, Object... para)
        throws SQLException
    {
        // 保证正整数
        pageNo = Math.max(pageNo, 1);
        pageSize = Math.max(pageSize, 1);
        
        // 查询记录总条数
        int index = sql.toLowerCase().indexOf(" from ");
        String countSql = "select count(1)" + StringUtils.substring(sql, index);
        long total = queryForLong(countSql, para);
        
        // 查询当前页数据
        StringBuilder sbSql = new StringBuilder(sql).append(" limit ").append(pageSize * (pageNo - 1)).append(", ").append(pageSize);
        List<Map<String, Object>> list = query(sbSql.toString(), para);
        
        // 封装返回分页对象
        PaginationSupport page = new PaginationSupport(total, pageNo, pageSize);
        page.setItems(list);
        return page;
    }
    
    /**
     * 带可变参数条件的分页查询
     * 
     * @param clazz
     * @param sql 查询sql
     * @param pageNo 页号
     * @param pageSize 每页记录数
     * @param para 可变参数
     * @return
     * @throws SQLException
     * @see [类、类#方法、类#成员]
     */
    public <T> PaginationSupport queryForPagination(T clazz, String sql, int pageNo, int pageSize, Object... para)
        throws SQLException
    {
        // 保证正整数
        pageNo = Math.max(pageNo, 1);
        pageSize = Math.max(pageSize, 1);
        
        // 查询记录总条数
        int index = sql.toLowerCase().indexOf(" from ");
        String countSql = "select count(1)" + StringUtils.substring(sql, index);
        long total = queryForLong(countSql, para);
        
        // 查询当前页数据
        StringBuilder sbSql = new StringBuilder(sql).append(" limit ").append(pageSize * (pageNo - 1)).append(", ").append(pageSize);
        List<T> list = query((Class)clazz, sbSql.toString(), para);
        
        // 封装返回分页对象
        PaginationSupport page = new PaginationSupport(total, pageNo, pageSize);
        page.setItems(list);
        return page;
    }
    
    /**
     * 带可变参数, 执行sql，返回执行影响的记录条数
     * 
     * @param sql 执行的sql 语句
     * @param para 可变参数
     * @return
     * @throws SQLException
     */
    public int update(String sql, Object... para)
        throws SQLException
    {
        LOGGER.info("executeUpdate: {}, para: {}", sql, ToStringBuilder.reflectionToString(para));
        Connection conn = null;
        int count = 0;
        try
        {
            conn = getConnection();
            count = runner.update(conn, sql, para);
        }
        catch (SQLException e)
        {
            LOGGER.error("------executeUpdate error------", e);
            throw e;
        }
        finally
        {
            if (conn != null && conn.getAutoCommit())
            {
                close();
            }
            LOGGER.info("DBPool execSql end ");
        }
        return count;
    }
}
